/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.tools;

import breviary.VideoThumbTaker;
import cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.main.BanBkE2p;
import static cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.main.BanBkE2p.lcBasePath;
import cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.service.FJRThreadTask;
import cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.tools.fastutil.FastPo;
import static cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.tools.fastutil.FileManagerConfig.CLIENT_CONFIG_FILE;
import cn.ac.iie.di.ban.ycl.bk.e2p.ysyb.tools.fastutil.FileManagerUtil;
import com.alibaba.rocketmq.common.UtilAll;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.log4j.Logger;

/**
 *
 * @author wmr
 */
public class JsonUtil {

    private static boolean testHuayan = true;
    private static Logger logger = Logger.getLogger(JsonUtil.class);
    final private static String ID = "ID";
    final private static String ID_VALUE = "GXB_SUSPICIOUS_INFO_SEQ.Nextval";
    final private static String SRC_TYPE_CODE = "SOURCETYPECODE";
    final private static String SRC_TYPE_NAME = "SOURCETYPENAME";
    final private static String INSERT_TIME = "INSERTTIME";
    final private static String FILE_SHOW_URL = "FILESHOWURL";
    final private static String FILE_URL = "FILEURL";
    final private static String FILE_ICON = "FILEICON";
//    final private static String SAMPLE_CODE = "SAMPLECODE";
    final private static String SAMPLE_CODE = "CODE";
    final private static String RESULT_TYPE = "RESULTTYPE";
    //新的字段
//    final private static String ISHARMFUL = "ISHARMFUL";
//    final private static String AUTOGK = "AUTOGK";
    final private static String SOURCE_FILE_URL = "SOURCEFILEURL";
    //来源方式及其编码
    private static String sourceTypeCode;
    private static String srcTypeName;
//    private static String orclFilePath = BanBkE2p.confDir + "/oracle_jdbc.properties";
    private static String orclFilePath = ClassLoader.getSystemClassLoader().getResource("oracle_jdbc.properties").getFile();
    private static String RESULT_TYPE_VALUE;
    //元信息初始化一次
    private static boolean metaDataInited = false;

    /*子表信息*/
    final private static int SPECIFIED_COL_NUM = 2;
    private static ArrayList<String> specifiedFields;//需要插入子表的列
    private static HashMap<String, ArrayList<String>> specifiedTableCols;//子表的列
    private static HashMap<String, String> specifiedSqlPrefixes;//子表sql语句前缀
    private static HashMap<String, String> specifiedTableNames;//子表的表名

    static {
        specifiedFields = new ArrayList<String>(SPECIFIED_COL_NUM);
        specifiedTableCols = new HashMap<String, ArrayList<String>>(SPECIFIED_COL_NUM);
        specifiedSqlPrefixes = new HashMap<String, String>(SPECIFIED_COL_NUM);
        specifiedTableNames = new HashMap<String, String>(SPECIFIED_COL_NUM);
    }
    /*数据字典*/
    final private static int DATA_MAP_NUM = 4;
    private static ArrayList<String> dataMapFields;//需要映射的列
    private static HashMap<String, String> dataMapSrcFields;//映射来源列
    private static HashMap<String, HashMap<String, String>> dataMapHashMaps;//映射表

    static {
        //需要映射的列
        dataMapFields = new ArrayList<String>(DATA_MAP_NUM);
        dataMapFields.add("TYPECODE");
//        dataMapFields.add("SOURCECODE");
        dataMapFields.add("SOURCEREPORTCODE");
//        dataMapFields.add("SOURCESITENAME");
        dataMapFields.add("SOURCENAME");
        dataMapFields.add("IPPROVINCETYPE");
        //映射来源
        dataMapSrcFields = new HashMap<String, String>(DATA_MAP_NUM);
        dataMapSrcFields.put("TYPECODE", "TYPENAME");
//        dataMapSrcFields.put("SOURCECODE", "SOURCENAME");
        dataMapSrcFields.put("SOURCEREPORTCODE", "SOURCEREPORTNAME");
//        dataMapSrcFields.put("SOURCESITENAME", "SOURCESITECODE");
        dataMapSrcFields.put("SOURCENAME", "SOURCECODE");
        dataMapSrcFields.put("IPPROVINCETYPE", "IPPROVINCETYPE");

        //映射表
        dataMapHashMaps = new HashMap<String, HashMap<String, String>>(DATA_MAP_NUM);

        String line;
        String[] dvd;
        for (String field : dataMapFields) {
            HashMap<String, String> curMap = new HashMap<String, String>();
            try {
//                String mataFilePath = BanBkE2p.confDir + "/" + field + ".mapdata";
                String mataFilePath = ClassLoader.getSystemClassLoader().getResource(field + ".mapdata").getFile();
                BufferedReader br = new BufferedReader(new FileReader(mataFilePath));
                try {
                    while ((line = br.readLine()) != null) {
                        line = line.trim();
                        if (line.isEmpty()) {
                        } else {
                            dvd = line.split("\t");
                            if (dvd.length == 2) {
                                curMap.put(dvd[0], dvd[1]);
                            }
                        }
                    }
                    dataMapHashMaps.put(field, curMap);
                } catch (Exception ex) {
                    dataMapHashMaps = null;
                    logger.error("cannot read mapData file : " + field + ".mapdata");
                } finally {
                    try {
                        br.close();
                    } catch (IOException ex) {
                    }
                }
            } catch (FileNotFoundException ex) {
                dataMapHashMaps = null;
                logger.error("cannot find mapData file : " + field + ".mapdata");
            }
        }
    }
    //jdbc信息
    private static String tbName = null;
    private static String new_half_sql = null;
    private static String new_cols = null;

    static {
        try {
            Properties prop = new Properties();
//            InputStream in = new FileInputStream("oracle_jdbc.properties");
            InputStream in = new FileInputStream(orclFilePath);
            prop.load(in);
            in.close();
            tbName = prop.getProperty("tablename").trim();
            specifiedFields.addAll(Arrays.asList(prop.getProperty("spCols").trim().split(",")));
            for (String str : prop.getProperty("spTableNames").trim().split(",")) {
                String[] dvd = str.split(":");
                if (dvd.length != 2) {
                    throw new Exception("cannot get spTableName ,with string \"" + str + "\"");
                }
                specifiedTableNames.put(dvd[0], dvd[1]);
            }
            new_half_sql = prop.getProperty("new_half_sql").trim();
            new_cols = prop.getProperty("new_cols").trim();
        } catch (Exception e) {
            logger.error(" Failed to get the JSON tablename " + e.getMessage(), e);
            tbName = null;
        }
    }

    public static boolean jsonData(String localFilePathData, List<Map<String, String>> dataFileList, String localFilePath) {
        LcUtil lcUtil = null;
        try {
            lcUtil = new LcUtil();
        } catch (Exception ex) {
            logger.error(ex.getMessage(), ex);
        }

//        List<String> sqlList = null;
        boolean flag = true;

        try {
            if (null == tbName) {
                //从oracle配置文件中获取表名、特殊列名、子表名、
                try {
                    Properties prop = new Properties();
                    InputStream in = new FileInputStream(orclFilePath);
                    prop.load(in);
                    in.close();
                    tbName = prop.getProperty("tablename").trim();
                    specifiedFields.clear();
                    specifiedFields.addAll(Arrays.asList(prop.getProperty("spCols").trim().split(",")));
                    specifiedTableNames.clear();
                    for (String str : prop.getProperty("spTableNames").trim().split(",")) {
                        String[] dvd = str.split(":");
                        if (dvd.length != 2) {
                            throw new Exception("cannot get spTableName ,with string \"" + str + "\"");
                        }
                        specifiedTableNames.put(dvd[0], dvd[1]);
                    }
                } catch (Exception e) {
                    logger.error(" Failed to get the JSON tablename" + e.getMessage(), e);
                    tbName = null;
                    return false;
                }
            }
            //映射表是否读取完整
            if (null == dataMapHashMaps) {
                dataMapHashMaps = new HashMap<String, HashMap<String, String>>(DATA_MAP_NUM);

                String line;
                String[] dvd;
                for (String field : dataMapFields) {
                    HashMap<String, String> curMap = new HashMap<String, String>();
                    try {
                        BufferedReader br = new BufferedReader(new FileReader(field + ".mapdata"));
                        try {
                            while ((line = br.readLine()) != null) {
                                line = line.trim();
                                if (line.isEmpty()) {
                                } else {
                                    dvd = line.split("\t");
                                    if (dvd.length == 2) {
                                        curMap.put(dvd[0], dvd[1]);
                                    }
                                }
                            }
                            dataMapHashMaps.put(field, curMap);
                        } catch (Exception ex) {
                            dataMapHashMaps = null;
                            logger.error("cannot read mapData file : " + field + ".mapdata");
                            return false;
                        } finally {
                            try {
                                br.close();
                            } catch (IOException ex) {
                            }
                        }
                    } catch (FileNotFoundException ex) {
                        dataMapHashMaps = null;
                        logger.error("cannot find mapData file : " + field + ".mapdata");
                        return false;
                    }
                }
            }
            //
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
            logger.info(" JsonUtil start " + df.format(new Date()));
            //sql
            Connection conn = null;
            Statement st = null;
            Statement stmt = null;
            Statement subSt = null;
            //文件是否可用
//            boolean flag = true;
            logger.info(" Start to get the JSON data tablename ");
            logger.info(" Get JSON data tablename: " + tbName);
            logger.info(" Start getting JSON files ");
            //获取需要处理的文件列表

            List<String> jsonFileList = new ArrayList<String>();
            Map<String, String> fileMap = new HashMap<String, String>();
            for (int i = 0; i < dataFileList.size(); i++) {
                Map<String, String> str = dataFileList.get(i);
                String lastFilename = str.get("name");
                String realName = lastFilename.substring(lastFilename.lastIndexOf("/") + 1, lastFilename.length());
                String dataFileName = localFilePathData + lastFilename;
                String okFileName = localFilePathData + lastFilename + ".ok";
                File dataf = new File(dataFileName);
                File okf = new File(okFileName);
//                if (okf.exists() && dataf.exists()) {
//                if (dataf.exists()) {
//                    if (realName.endsWith(".json")) {
//                        jsonFileList.add(lastFilename);
//                    }
//                }
                if (dataf.exists()) {
                    if (realName.endsWith(".json")) {
                        jsonFileList.add(lastFilename);
                    } else {
                        String md5N = realName.substring(0, realName.lastIndexOf("."));
                        fileMap.put(md5N, lastFilename);

                    }
                }
            }

            if (jsonFileList.size() == 0) {
                FJRThreadTask.errorFlag = 1;
                flag = false;
                logger.error(" JSON file does not exist");
                return flag;
            }

            if (fileMap.size() == 0) {
                FJRThreadTask.errorFlag = 1;
                flag = false;
                logger.error(" data file does not exist");
                return flag;
            }
            //得到sql connection
//                    conn = new JdbcUtil().getConnection(conn);
            int conn_Count = 0;
            while (true) {
//                        logger.info("connection Oracle " + conn_Count + "time -------------");
                conn = new JdbcUtil().getConnection(conn);

                if (conn != null) {
//                    conn.setAutoCommit(false);
                    logger.info("connection Oracle " + conn_Count + "time success-------------");
                    break;
                }
                conn_Count++;

            }
            if (conn == null) {
                FJRThreadTask.errorFlag = 1;
                flag = false;
                logger.error(" Database connection failed");
                return flag;
            }
            conn.setAutoCommit(false);
            st = conn.createStatement();
            stmt = conn.createStatement();
            subSt = conn.createStatement();
//            List<String> sqlList = new ArrayList<String>();
            String nameSpace = localFilePath.substring(localFilePath.lastIndexOf("/") + 1, localFilePath.length());
            String nameSpaceCode = nameSpace.split("_")[0];
            RESULT_TYPE_VALUE = nameSpaceCode;
            sourceTypeCode = "HTDR";
            srcTypeName = "后台导入";
            //处理每个文件
            for (int k = 0; k < jsonFileList.size(); k++) {
//                List<String> sqlList = new ArrayList<String>();
                Map<String, String> sqlMap = new HashMap<String, String>();
                Map<String, List<String>> subSqlMap = new HashMap<String, List<String>>();
                String jsonFilePath = null;
                if (flag) {

                    jsonFilePath = localFilePathData + jsonFileList.get(k);
                    logger.info(" Get JSON file path: " + jsonFilePath + "   " + df.format(new Date()));
                    List<String> fieldList = new ArrayList<String>();
                    //准备组装sql语句
                    String halfsql = "INSERT INTO " + tbName + " (" + SRC_TYPE_CODE + "," + SRC_TYPE_NAME + "," + ID + "," + INSERT_TIME + "," + FILE_URL + "," + FILE_ICON + "," + SAMPLE_CODE + "," + FILE_SHOW_URL + "," + SOURCE_FILE_URL + "," + RESULT_TYPE;
                    for (String spCol : specifiedFields) {
                        halfsql += "," + spCol;
                    }
                    for (String dmapCol : dataMapFields) {
                        halfsql += "," + dmapCol;
                    }
                    halfsql += ",";
                    //读取json文件内容
                    JsonUtil jsonUtil = new JsonUtil();
                    String JsonContext = jsonUtil.ReadFile(jsonFilePath);
                    if (JsonContext == null) {

                        FJRThreadTask.errorFlag = 1;
                        flag = false;
                        logger.error("failed to get JSON string");
                        return flag;
                    }
                    logger.info(" JsonContext -------- " + JsonContext);
                    JSONObject ob;
                    try {
                        ob = JSONObject.fromObject(JsonContext);
                    } catch (Exception e) {
                        FJRThreadTask.errorFlag = 1;
                        flag = false;
                        logger.error(" JSON file error" + e.getMessage(), e);
                        return flag;
                    }
                    
                    String jsonSrc = ob.get("src").toString().toUpperCase();
                    
                    if (jsonSrc == null || jsonSrc.equals("") || jsonSrc.equals(" ")) {
                        logger.info("jsonSrc error !!!!!  -----------------------!!");
                        continue;
                    } else if ("JINGWAIYOUHAI".equals(jsonSrc)) {
                        jsonSrc = "JINGWAI";
                        logger.warn("JINGWAIYOUHAI to JINGWAI :"+jsonSrc);
                    }
                    Date dt = new Date();
                    SimpleDateFormat matter1 = new SimpleDateFormat("yyyyMMdd");
                    String dataTime = matter1.format(dt);
                    String lcComPath = lcBasePath + "/" + jsonSrc + "/" + dataTime + "/";
                    //得到metaData，获得列名,加入fieldList，并组装sql中key部分
                    JSONArray fieldArray = ob.getJSONArray("record_meta");
                    for (int i = 0; i < fieldArray.size(); i++) {
                        JSONObject jsonObject = fieldArray.getJSONObject(i);
                        logger.info(" fieldname: " + "[" + i + "]name=" + jsonObject.get("name"));
                        String fieldName = jsonObject.get("name").toString().toUpperCase().trim();
                        //在主表插入时排除子表字段 
                        //SAMPLEID,FEATURETYPE,FEATURENAME,FEATUREID,FEATURECOUNT,SCORE,OTHER,ENGINE (new table cols)
                        if (fieldName.equals(INSERT_TIME) || dataMapFields.contains(fieldName) || fieldName.equals(FILE_ICON) || fieldName.equals(FILE_URL) || fieldName.equals(RESULT_TYPE)) {
//						System.out.println("---\t" + fieldName);
                        } else if (fieldName.equals("SAMPLEID") || fieldName.equals("FEATURETYPE") || fieldName.equals("FEATURENAME") || fieldName.equals("FEATUREID") || fieldName.equals("FEATURECOUNT") || fieldName.equals("SCORE") || fieldName.equals("OTHER") || fieldName.equals("ENGINE")) {
                        } else if (specifiedFields.contains(fieldName)) {
                            if (!metaDataInited) {
                                //get columns
                                ArrayList<String> colNames = getColNames(jsonObject.getJSONArray("type"));
                                //set to columns table
                                specifiedTableCols.put(fieldName, colNames);
                                //build sql prefix
                                StringBuilder sb = new StringBuilder("insert into ");
                                sb.append(specifiedTableNames.get(fieldName)).append(" ( ").append(fieldName.startsWith("DWD") ? "DWDCODE" : "REMARKCODE").append(",");
//                            sb.append(specifiedTableNames.get(fieldName)).append(" ( ").append(fieldName.startsWith("DWD")?"DWDCODE":"REMARKCODE").append(",");
                                for (String col : colNames) {
                                    sb.append(col).append(",");
                                }
                                int idx = sb.lastIndexOf(",");
                                try {
                                    sb.replace(idx, idx + 1, " ) values( ");
                                } catch (Exception e) {
                                    int a = 0;
                                }
                                specifiedSqlPrefixes.put(fieldName, sb.toString());
                            }
                        } else {
                            halfsql = halfsql + fieldName + ",";
//						System.out.println(fieldName);
                            fieldList.add(fieldName);
                        }
                    }
                    metaDataInited = true;
                    //组装sql
                    halfsql = halfsql.substring(0, halfsql.length() - 1) + ") VALUES (" +/* 0 +*/ "'" + sourceTypeCode + "','" + srcTypeName + "',";
                    logger.info(" half-sql ------- " + halfsql);

                    //获得并处理每条记录
                    JSONArray dataArray = ob.getJSONArray("record_list");

                    if (dataArray.size() == 0) {
                        FJRThreadTask.errorFlag = 2;
                        flag = false;
                        logger.error(" data file count is 0 **************");
                        stmt.close();
                        st.close();
                        subSt.close();
                        conn.close();
                        return flag;
                    }

//                    if (fDfsRetStrMap.size() == dataArray.size()) {
                    for (int i = 0; i < dataArray.size(); i++) {
                        String uniqId = getUniqueSeq(conn, stmt);
                        if (null == uniqId) {
                            logger.error(" getUniqueSequence failed");
                            stmt.close();
                            st.close();
                            subSt.close();
                            conn.close();
                            return false;
                        }
                        JSONObject jsonObject = dataArray.getJSONObject(i);
                        String fileMd5 = jsonObject.get("FILEMD5").toString();
                        String lcMd5Path;
                        if (fileMd5 == null || fileMd5.equals("") || fileMd5.equals(" ")) {
                            logger.info("fileMd5 error !!!!!  -----------------------!!");
                            continue;
                        } else {
                            String subFileMd5 = fileMd5.substring(0, 2);
                            lcMd5Path = lcComPath + subFileMd5 + "/" + fileMd5;
                        }

                        String dataFileName = fileMap.get(fileMd5);
                        if (dataFileName == null) {
                            logger.info("no datafile match json -----------------------!!");
                            continue;
                        }
                        String fileNameData = fileMap.get(fileMd5).substring(fileMap.get(fileMd5).lastIndexOf("/") + 1, fileMap.get(fileMd5).length());;
                        String oldFilePath = localFilePathData + fileMap.get(fileMd5);
                        String lcLastPath = lcMd5Path + "/" + fileNameData;

//                        lcUtil.copyFile(oldFilePath, lcLastPath);
//                        logger.info( " oldFilePath: " + oldFilePath);
//                        logger.info( " lcLastPath: " + lcLastPath);
////                        logger.info(nameSpace);
                        while (true) {
                            boolean copyflag = lcUtil.copyFile(oldFilePath, lcLastPath);
                            if (copyflag) {
                                logger.info(" data file  copy succ end ");
                                break;
                            }
                        }

//                        String uuid = UUID.randomUUID().toString().replace("-", "");
//                        String pngFilePath = localPngPath + uuid + ".png";
//                        String lcPngFilePath = lcPngPath + uuid + ".png";
                        String localPngPath = oldFilePath.substring(0, oldFilePath.lastIndexOf("/") + 1) + "picture/";
                        String lcPngPath = lcLastPath.substring(0, lcLastPath.lastIndexOf("/") + 1) + fileMd5 + "_1.png";
                        File newPngPath = new File(localPngPath);
                        //判断文件夹是否存在 
                        if (newPngPath != null && !newPngPath.exists()) {
                            newPngPath.mkdirs();
                        }
                        String pngFilePath = localPngPath + fileMd5 + "_1.png";
//                      String ffmpegPath = "/usr/local/ffmpeg/bin/ffmpeg";
                        String ffmpegPath = BanBkE2p.ffmpegPath;
                        VideoThumbTaker videoThumbTaker = new VideoThumbTaker(ffmpegPath);
                        try {
                            logger.info("localFilePath  ---------------" + localFilePath);
                            logger.info("pngFilePath  ---------------" + pngFilePath);
                            logger.info("lcPngPath  ---------------" + lcPngPath);
//                            videoThumbTaker.getThumb(oldFilePath, pngFilePath, 176, 144, 0, 0, 1);
                            videoThumbTaker.getThumb(oldFilePath, lcPngPath, 176, 144, 0, 0, 1);
                            logger.info(oldFilePath + "create picture success !!!");
                        } catch (Exception e) {
                            e.printStackTrace();
                            logger.error(oldFilePath + "create picture failed !!!" + e.getMessage(), e);
                        }

//                        File pangF = new File(pngFilePath);
                        File pangF = new File(lcPngPath);
                        boolean sizeFlag = false;
                        if (pangF.exists() && pangF.isFile()) {
                            logger.info(pangF.length());
                            long pangSize = pangF.length();
                            if (pangSize == 0) {
                                sizeFlag = false;
                            } else {
                                sizeFlag = true;
//                                while (true) {
//                                    boolean copyflag = lcUtil.copyFile(pngFilePath, lcPngPath);
//                                    if (copyflag) {
//                                        logger.info(" png file  copy succ end ");
//                                        break;
//                                    }
//                                }
                            }
                        } else {
                            logger.error("file pangFilePath doesn't exist or is not a file");
                        }

//                        if (sizeFlag) {
//                            lcUtil.copyFile(pngFilePath, lcPngPath);
//                        }else{
//                        
//                        }
                        //处理主表字段
                        String SOURCE_CODE_VALUE = jsonObject.get("SOURCECODE").toString();
                        if (SOURCE_CODE_VALUE.equals("JINGWAI")) {
                            RESULT_TYPE_VALUE = "7";
                        }
                        String TYPE_NAME_VALUE = jsonObject.get("TYPENAME").toString();
                        String FILE_SHOW_URL_VALUE = "";
                        if (TYPE_NAME_VALUE.equals("图片")) {
//                            FILE_SHOW_URL_VALUE = fastPo.getFilePath();
                            FILE_SHOW_URL_VALUE = lcLastPath;
                        } else {
                            FILE_SHOW_URL_VALUE = "";
                        }
                        String fileiconValue = "default";
                        if (sizeFlag) {
                            fileiconValue = "'" + lcPngPath + "'";
                        } else {
                            fileiconValue = "default";
//                            fileiconValue = "'" + fastPo.getPangPath() + "'";
                        }

//                            String RESULT_TYPE_VALUE = "1";
                        String SOURCE_FILE_URL_VALUE = "";
                        Object oUrlV = jsonObject.get("FILEURL");
                        if (oUrlV == null) {
                            SOURCE_FILE_URL_VALUE = "default";
                        } else {
                            SOURCE_FILE_URL_VALUE = "'" + jsonObject.get("FILEURL").toString() + "'";
                        }

//                        String SOURCE_FILE_URL_VALUE = jsonObject.get("FILEURL").toString();
                        String sql = halfsql + ID_VALUE + ",'" + new SimpleDateFormat(UtilAll.yyyyMMddHHmmss).format(new Date())/*INSERT_TIME*/ + "','" + lcLastPath /*FILE_URL*/ + "'," + fileiconValue /*FILE_ICON*/ + ",'" + uniqId/*SAMPLE_CODE*/ + "','" + FILE_SHOW_URL_VALUE + "'," + SOURCE_FILE_URL_VALUE + ",'" + RESULT_TYPE_VALUE + "'";

                        for (int tti = 0; tti < specifiedFields.size(); ++tti) {
                            Object oInfo = jsonObject.get(specifiedFields.get(tti));
                            if (oInfo == null) {
                                sql += ",default";
                                continue;
                            } else {
                                String dInfo = jsonObject.get(specifiedFields.get(tti)).toString();
                                if (dInfo == null || dInfo.equals("")) {
//                                sql += "','";
                                    sql += ",default";
                                    continue;
                                }

                            }

                            sql += ",'" + uniqId + "'";
                        }
                        for (String dataMapCol : dataMapFields) {
                            String data = dataMapHashMaps.get(dataMapCol).get(jsonObject.get(dataMapSrcFields.get(dataMapCol)));
                            if (data == null || data.equals("")) {
//                                sql += "','";
                                sql += ",default";
                                continue;
                            }
                            sql += ",'" + dataMapHashMaps.get(dataMapCol).get(jsonObject.get(dataMapSrcFields.get(dataMapCol))) + "'";
                        }
                        sql += ",";
                        String data = new String();
//                        String discoveredtime = "";
                        for (int j = 0; j < fieldList.size(); j++) {
                            String fieldData = "";
                            String fieldN = fieldList.get(j);

                            logger.info("fieldN *************************** " + fieldN);
                            Object oData = jsonObject.get(fieldN);
                            if (oData == null) {
                                fieldData = "default";
                            } else {
                                String sdata = jsonObject.get(fieldN).toString();
                                if (sdata == null || sdata.equals("")) {
//                                sql += "','";
                                    fieldData = "default";
//                                continue;
                                } else {
                                    String jsVa = jsonObject.get(fieldN).toString();
                                    if (jsVa.contains("'")) {
                                        jsVa = jsVa.replaceAll("'", "''");
                                        fieldData = "'" + jsVa + "'";
                                    } else {
                                        fieldData = "'" + jsonObject.get(fieldN).toString() + "'";
                                    }
//                                fieldData = "'" + jsonObject.get(fieldN).toString() + "'";
                                }

                            }

//                            if (fieldN == "DISCOVEREDTIME") {
//                                discoveredtime = fieldData;
//                            } else if (fieldN == "PROCESSINGTIME") {
//                                fieldData = "@@PROCESSINGTIME@@";
//                            }
                            data = data + fieldData + ",";
                        }
//                        data = data.replace("@@PROCESSINGTIME@@", discoveredtime);
                        sql += data.substring(0, data.length() - 1) + ")";

                        logger.info(" sql --------- " + sql);
                        ///////////////////////////////////
//                        String fastPath = fastPo.getFilePath();
//                        String pngPath = fastPo.getPangPath();
                        if (RESULT_TYPE_VALUE.equals("0")) {
                            boolean flagI = true;
//                                logger.info("file not exist --------------------");
//                            flagI = JdbcUtil.insert(conn, sql, st);
                            sqlMap.put(sql, lcLastPath);
//                            sqlList.add(sql);
                            st.addBatch(sql);
                            if (!flagI) {
//                                JsonUtil.delFastF(fastPath, pngPath);
//                                    FJRThreadTask.errorFlag = 2;
                                FJRThreadTask.errorFlag = 2;
                                flag = false;
                                logger.error("insert info error ------------------ jsonpath: " + jsonFilePath);
//                                break;
                                continue;
                            }
                            logger.info("insert table info success ******************");

                            //sub table data
                            int ins_count = 0;
                            for (String spCol : specifiedFields) {
                                Object oInfo = jsonObject.get(spCol);
                                if (oInfo == null) {
                                    continue;
                                } else {
                                    String dInfo = jsonObject.get(spCol).toString();
                                    if (dInfo == null || dInfo.equals("")) {
                                        continue;
                                    }
                                }

                                JSONArray spArray = jsonObject.getJSONArray(spCol);

                                List<String> subList = new ArrayList<String>();
                                for (int ssti = 0; ssti < spArray.size(); ++ssti) {
                                    JSONObject spObj = spArray.getJSONObject(ssti);
                                    logger.info("ssti: " + ssti);
                                    logger.info("spCol: " + spCol);
                                    String sq = specifiedSqlPrefixes.get(spCol);
                                    logger.info("sq: " + sq);
                                    StringBuilder sb = new StringBuilder(specifiedSqlPrefixes.get(spCol)).append("'").append(uniqId).append("'");
                                    for (String col : specifiedTableCols.get(spCol)) {
//                                        sb.append(",'").append(spObj.get(col)).append("'");
                                        Object ouV = spObj.get(col);
                                        if (ouV == null) {
                                            sb.append(",").append("default");
                                        } else {
                                            String suV = spObj.get(col).toString();
                                            if (suV.contains("'")) {
                                                sb.append(",'").append(suV.replaceAll("'", "''")).append("'");
                                            } else {
                                                sb.append(",'").append(spObj.get(col)).append("'");
                                            }
                                        }

                                    }
//							int idx = sb.lastIndexOf(",", 0);
//							sb.replace(idx, idx + 1, " ) ");
                                    sb.append(")");
                                    logger.info("subTable sql --- --- " + sb.toString());
                                    subSt.addBatch(sb.toString());
                                    subList.add(sb.toString());
//                                    flagI = JdbcUtil.insert(conn, sb.toString(), st);
                                    ins_count++;
                                    if (!flagI) {
                                        logger.error("insert error " + sb.toString() + "------------------ jsonpath: " + jsonFilePath);
                                    }
                                }
                                subSqlMap.put(sql, subList);
                            }
                        } else {
//                                String new_sql = new_half_sql;
                            String[] n_cols = new_cols.split(",");
                            String n_data = new String();
                            for (int c = 0; c < n_cols.length; c++) {
                                String n_col = n_cols[c];
                                String colData = "";
                                logger.info("n_col *************************** " + n_col);

                                Object odata = jsonObject.get(n_col);
                                if (odata == null) {
                                    colData = "default";
                                } else {
                                    String sdata = jsonObject.get(n_col).toString();

                                    if (sdata == null || sdata.equals("")) {
//                                sql += "','";
                                        colData = "default";
//                                continue;
                                    } else {
                                        String jsV = jsonObject.get(n_col).toString();
                                        if (jsV.contains("'")) {
                                            jsV = jsV.replaceAll("'", "''");
                                            colData = "'" + jsV + "'";
                                        } else {
                                            colData = "'" + jsonObject.get(n_col).toString() + "'";
                                        }
                                    }

                                }

                                n_data = n_data + colData + ",";
                            }

                            String new_sql = new_half_sql + "GXB_SUSPICIOUS_ENGINE_SEQ.Nextval" + ",'" + "#CODE#" + "'," + n_data.substring(0, n_data.length() - 1) + ")";
                            logger.info("TABLE ENGINE SQL: " + new_sql);
//select count(*) from GXB_SUSPICIOUS_INFO where FILEID = 'sNEqdeIgiGw_' and SOURCECODE = 'MIAOPAI' and INSERTTIME >= to_char(sysdate-3,'yyyymmdd')
                            String dataFILEID = jsonObject.get("FILEID").toString();
                            String dataSOURCECODE = jsonObject.get("SOURCECODE").toString();
//                            String c_sql = "select count(*) from GXB_SUSPICIOUS_INFO where FILEID = '#DATAFILEID#' and SOURCECODE = '#DATASOURCECODE#' and INSERTTIME >= to_char(sysdate-3,'yyyymmdd')";
                            String c_sql = "select CODE from GXB_SUSPICIOUS_INFO where FILEID = '#DATAFILEID#' and SOURCECODE = '#DATASOURCECODE#' and INSERTTIME >= to_char(sysdate-3,'yyyymmdd')";
                            c_sql = c_sql.replaceAll("#DATAFILEID#", dataFILEID);
                            c_sql = c_sql.replaceAll("#DATASOURCECODE#", dataSOURCECODE);
                            logger.info("exitfile selsql count: " + c_sql);
//                            int fileCount = JdbcUtil.selFile(conn, c_sql);
                            List<String> codeLs = JdbcUtil.selFile(conn, c_sql, st);
                            boolean eFlag = true;
                            boolean iFlag = true;
//                                String fastPath = fastPo.getFilePath();
//                                String pngPath = fastPo.getPangPath();
                            if (codeLs.size() >= 1) {
//                                JsonUtil.delFastF(fastPath, pngPath);
                                String codeRe = codeLs.get(0);
                                if (codeRe.equals("errorCODE")) {
                                    flag = false;
                                    logger.error("select code error ------------------");
                                    break;
                                }
                                logger.info("file exist --------------------");
                                new_sql = new_sql.replaceAll("#CODE#", codeRe);
                                logger.info("insert1 table engine new_sql: " + new_sql);
                                eFlag = JdbcUtil.insert(conn, new_sql, st);

                                if (!eFlag) {
//                                    flag = false;
                                    logger.error("insert engine error1 ------------------ jsonpath: " + jsonFilePath);
//                                    break;
                                }
                                logger.info("insert table engine success1 ******************");
                            } else if (codeLs.size() == 0) {
                                logger.info("file not exist --------------------");
                                iFlag = JdbcUtil.insert(conn, sql, st);
                                if (!iFlag) {
//                                        JsonUtil.delFastF(fastPath, pngPath);
//                                        FJRThreadTask.errorFlag = 2;
                                    FJRThreadTask.errorFlag = 1;
                                    flag = false;
                                    logger.error("insert info error ------------------ jsonpath: " + jsonFilePath);
                                    break;
                                }
                                logger.info("insert table info success ******************");
                                new_sql = new_sql.replaceAll("#CODE#", uniqId);

                                logger.info("insert2 table engine new_sql: " + new_sql);

                                eFlag = JdbcUtil.insert(conn, new_sql, st);

                                if (!eFlag) {
//                                    flag = false;
                                    logger.error("insert engine error2 ------------------ jsonpath: " + jsonFilePath);
//                                    break;
                                }
                                logger.info("insert table engine success2 ******************");

                                //sub table data
                                int ins_count = 0;
                                for (String spCol : specifiedFields) {
                                    Object oInfo = jsonObject.get(spCol);
                                    if (oInfo == null) {
                                        continue;
                                    } else {
                                        String dInfo = jsonObject.get(spCol).toString();
                                        if (dInfo == null || dInfo.equals("")) {
                                            continue;
                                        }
                                    }

                                    JSONArray spArray = jsonObject.getJSONArray(spCol);
                                    for (int ssti = 0; ssti < spArray.size(); ++ssti) {
                                        JSONObject spObj = spArray.getJSONObject(ssti);
                                        StringBuilder sb = new StringBuilder(specifiedSqlPrefixes.get(spCol)).append("'").append(uniqId).append("'");
                                        for (String col : specifiedTableCols.get(spCol)) {
                                            Object ouV = spObj.get(col);
                                            if (ouV == null) {
                                                sb.append(",").append("default");
                                            } else {

                                                String suV = spObj.get(col).toString();
                                                if (suV.contains("'")) {
                                                    sb.append(",'").append(suV.replaceAll("'", "''")).append("'");
                                                } else {
                                                    sb.append(",'").append(spObj.get(col)).append("'");
                                                }
                                            }

                                        }
//							int idx = sb.lastIndexOf(",", 0);
//							sb.replace(idx, idx + 1, " ) ");
                                        sb.append(")");
                                        logger.info("subTable sql --- --- " + sb.toString());
                                        iFlag = JdbcUtil.insert(conn, sb.toString(), st);
                                        ins_count++;
                                        if (!iFlag) {
//                                            FJRThreinsert engine error2 adTask.errorFlag = 4;
//                                            flag = false;
                                            logger.error("insert error " + sb.toString() + "------------------ jsonpath: " + jsonFilePath);
//                                            break;
                                        }
                                    }
                                }

                            } else {
//                                    JsonUtil.delFastF(fastPath, pngPath);
//                                    FJRThreadTask.errorFlag = 2;
                                FJRThreadTask.errorFlag = 1;
                                logger.error("select error filecount ----------- ");
                                flag = false;
                                break;
                            }
                        }
//                            
                    }//end handle record   
                }// end if (flag ) ** if bad data

                boolean batFlag = true;

                try {
                    logger.info("oracle  batch start execute !!!");
                    int[] co = st.executeBatch();
                    logger.info("oracle  batch execute end ! update num " + co.length);
//                    int[] subCo = subSt.executeBatch();
                } catch (SQLException ex) {
                    batFlag = false;

                    logger.error(" oracle sql batch error " + ex.getMessage(), ex);
                }
                if (!batFlag) {
                    conn.rollback();
                    for (Map.Entry<String, String> entry : sqlMap.entrySet()) {
                        System.out.println(entry.getKey() + ": " + entry.getValue());
                        String sql = entry.getKey();
                        boolean flagI = JdbcUtil.insert(conn, sql, st);
                        if (!flagI) {
//                            String fastPath = entry.getValue().getFilePath();
//                            String pngPath = entry.getValue().getPangPath();
//                            JsonUtil.delFastF(fastPath, pngPath);
                            FJRThreadTask.errorFlag = 2;
                            flag = false;
                            logger.error("insert info error ------------------ jsonpath: " + jsonFilePath);
                            continue;
                        }
                        logger.info("insert table info success ******************");
                        List sList = subSqlMap.get(sql);
                        if (sList == null || sList.size() == 0) {
                            continue;
                        } else {
                            for (int s = 0; s < sList.size(); s++) {
                                String subSql = sList.get(s).toString();
                                flagI = JdbcUtil.insert(conn, subSql, st);
                                if (!flagI) {
                                    logger.error("insert error " + subSql + "------------------ jsonpath: " + jsonFilePath);
                                }
                            }

                        }

                    }
                } else {
                    boolean subBatch = true;
                    try {
                        logger.info("oracle  sub batch start execute !!!");
                        int[] subCo = subSt.executeBatch();
                        logger.info("oracle  sub batch execute end ! update num " + subCo.length);
                    } catch (SQLException ex) {
                        subBatch = false;
                        logger.error(" oracle sql batch error " + ex.getMessage(), ex);
                    }
                    if (!subBatch) {
                        for (Map.Entry<String, List<String>> entry : subSqlMap.entrySet()) {
                            List sList = entry.getValue();
                            for (int s = 0; s < sList.size(); s++) {
                                String subSql = sList.get(s).toString();
                                boolean flagS = JdbcUtil.insert(conn, subSql, st);
                                if (!flagS) {
                                    logger.error("insert error " + subSql + "------------------ jsonpath: " + jsonFilePath);
                                }
                            }
                        }

                    }
                }
                conn.commit();
                logger.info("commit data success ! ----------------------");
                st.clearBatch();
                subSt.clearBatch();

            }//end handle json file
            subSt.close();
            stmt.close();
            st.close();
            conn.close();
            conn = null;
            logger.info(" JsonUtil oracle conn close " + df.format(new Date()));

            logger.info(" JsonUtil end " + df.format(new Date()));
        } catch (Exception ex) {
            FJRThreadTask.errorFlag = 1;
            flag = false;
            logger.error(" parse jsonfile error !!!!!!!! " + ex.getMessage(), ex);
        }
        return flag;
    }

    private static ArrayList<String> getColNames(JSONArray fieldArray) {
        ArrayList<String> rtn = new ArrayList<String>();
        for (int i = 0; i < fieldArray.size(); i++) {
            JSONObject jsonObject = fieldArray.getJSONObject(i);
            String fieldName = jsonObject.get("name").toString().toUpperCase();
            rtn.add(fieldName);
        }
        return rtn;
    }

    public String ReadFile(String Path) {
        BufferedReader reader = null;
        String laststr = "";
        try {
            FileInputStream fileInputStream = new FileInputStream(Path);
            InputStreamReader inputStreamReader = new InputStreamReader(fileInputStream, "UTF-8");
            reader = new BufferedReader(inputStreamReader);
            String tempString = null;
            while ((tempString = reader.readLine()) != null) {
                laststr += tempString;
            }
            reader.close();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }
        return laststr;
    }
//    public static void main(String[] args) {
//        jsonData("D:\\jsontest\\apkinfo-complex.json", "subjobid");
//    }
    final static private String UNIQUE_DEF_STRING;

    static {
        UNIQUE_DEF_STRING = PropsFiles.getValue("jettyServerIP") + ":" + PropsFiles.getValue("jettyServerPort") + "_";
    }
    private static long seq = 0;

    static private synchronized long getNextSeq() {
        return ++seq;
    }

    static private synchronized String getUniqueSeq(Connection conn, Statement stmt) {
//        if (testHuayan) {
//            return "" + getNextSeq();
//        }
        String code = "";
        try {
//            Statement stmt = conn.createStatement();
            ResultSet rslt = stmt.executeQuery("select sys_guid() code from dual");
            if (rslt.next()) {
//                rslt.getString(1);
                code = rslt.getString("CODE");
            }
        } catch (SQLException ex) {
            logger.error("getUniqueSeq err: " + ex.getMessage(), ex);
        }
//        return null;
        return code;
    }

    public static void delFastF(String fileAbsolutePath, String pangAbsolutePath) {

        FileManagerUtil fileManager = new FileManagerUtil();

        if (!fileAbsolutePath.equals("null")) {
//                        String fileGroup = fileAbsolutePath.substring(27, 33);
            logger.info("del fastDataFile start --------------------------");
            String[] paths = fileAbsolutePath.split("/");
            String fileGroup = paths[3];
            logger.info("fileGroup = " + fileGroup);
//                        String errorFilePath = fileAbsolutePath.substring(34, fileAbsolutePath.length());
            String errorFilePath = "";
            for (int i = 4; i < paths.length; i++) {
                errorFilePath += paths[i] + "/";
            }
            errorFilePath = errorFilePath.substring(0, errorFilePath.length() - 1);
            logger.info("filePath = " + errorFilePath);
            try {
                fileManager.delFile(fileGroup, errorFilePath);
                logger.info("del fastDataFile success --------------------------");
            } catch (Exception ex) {
                logger.error("delete fast png " + fileAbsolutePath + " error" + ex.getMessage(), ex);
            }
            logger.info("del fastDataFile end --------------------------");
        }
        if (!pangAbsolutePath.equals("null")) {
            logger.info("del fastPngFile start --------------------------");
            String errorPngPath = "";
            String[] paths = pangAbsolutePath.split("/");
            String pngGroup = paths[3];
            logger.info("pngGroup = " + pngGroup);
//                        String errorFilePath = fileAbsolutePath.substring(34, fileAbsolutePath.length());
            for (int i = 4; i < paths.length; i++) {
                errorPngPath += paths[i] + "/";
            }
            errorPngPath = errorPngPath.substring(0, errorPngPath.length() - 1);
            logger.info("errorPngPath = " + errorPngPath);
            try {
                fileManager.delFile(pngGroup, errorPngPath);
                logger.info("success del dataFile -----------------------------");
            } catch (Exception ex) {
                logger.error("delete fast png " + pangAbsolutePath + " error" + ex.getMessage(), ex);
            }
            logger.info("del fastPngFile end --------------------------");
        }

    }

    public static void main() {
    }
}
